Willkommen zur Lerneinheit über die Transaction Control Language (TCL) und über Stored Procedures. In dieser Einheit wird erläutert, wie komplexe Abläufe in einer Datenbank sicher, konsistent und effizient gestaltet werden können, auch dann, wenn viele Benutzer gleichzeitig zugreifen.

Weshalb sind Transaktionen notwendig? Hier ein Beispiel: Bei einer Geldüberweisung muss das System sowohl die Belastung des Absenders als auch die Gutschrift beim Empfänger korrekt durchführen. Kommt es während des Prozesses zu einem Fehler, dürfen keine inkonsistenten Zustände entstehen. Das Geld wurde abgebucht, aber nicht mehr gutgebucht. Es ist verschwunden! Transaktionen bündeln zusammengehörige Schritte und sorgen dafür, dass sie entweder vollständig oder gar nicht ausgeführt werden.

Das ACID-Modell beschreibt die Anforderungen an eine Transaktion. Die erste Eigenschaft ist Atomarität. Das bedeutet, dass eine Transaktion eben entweder vollständig ausgeführt oder vollständig rückgängig gemacht wird. Teilweise Ausführungen sind nicht erlaubt, wodurch eine hohe Verlässlichkeit gewährleistet wird. Ebenso bleibt das System unverändert, wenn eine atomare Transaktion abgebrochen wird. Die zweite Eigenschaft ist Konsistenz. Nach Abschluss einer Transaktion müssen alle Regeln und Integritätsbedingungen der Datenbank weiterhin gültig sein, wenn sie es auch am Anfang der Transaktion war.

Zusätzlich kommen die Eigenschaften Isolation und Dauerhaftigkeit hinzu. Isolation bedeutet, dass parallele Transaktionen sich nicht gegenseitig beeinflussen dürfen, das heißt, sie sind voneinander abgeschirmt. Veränderungen einer Transaktion dürfen für andere erst sichtbar werden, wenn die Transaktion abgeschlossen ist. Die Dauerhaftigkeit besagt, dass einmal abgeschlossene Transaktionen dauerhaft gespeichert sind, auch bei einem plötzlichen Stromausfall oder Systemabsturz. Technisch wird dies durch sogenannte Transaktionslogs unterstützt, die sicherstellen, dass alle bestätigten Änderungen rekonstruierbar sind.

Der technische Ablauf einer Transaktion beginnt in MariaDB mit der Deaktivierung von Auto-Commit, also dem automatischen Speichern einzelner Befehle. Anschließend werden die SQL-Befehle ausgeführt. Nach erfolgreicher Ausführung wird die Transaktion mit dem Befehl Commit dauerhaft gespeichert. Im Fehlerfall kann die gesamte Transaktion mit Rollback zurückgesetzt werden.

Ein häufiger Fehlerfall bei parallelen Transaktionen ist die sogenannte Blockierung. Eine Blockierung tritt auf, wenn eine Transaktion auf eine Ressource zugreifen möchte, etwa eine bestimmte Datenzeile, die von einer anderen Transaktion noch gesperrt ist. Problematisch wird es, wenn sich zwei Transaktionen gegenseitig blockieren – Transaktion A wartet auf eine Ressource, die von Transaktion B gesperrt wurde, und gleichzeitig wartet Transaktion B auf eine Ressource, die Transaktion A gesperrt hat. In diesem Fall entsteht eine sogenannte Verklemmung, im Englischen Deadlock genannt.

Schauen wir uns ein Beispiel zur Umsetzung von Transaktionen in Java mit der Java Database Connectivity an. Wir haben eine Konto-Tabelle in der Datenbank „Bank“ und wollen einen Geldbetrag von einer Person „X“ zu einer Person „Üpsilon“ überweisen. Das hier ist die Datenbanktabelle in der Ansicht von phpMyAdmin.

Für eine Transaktion wird zuerst das Auto-Commit deaktiviert. Denn sonst würde nach jeder einzelnen SQL-Anweisung direkt ein Commit stattfinden. Mehrere Anweisungen könnten dann nicht mehr ganz oder gar nicht ausgeführt werden, weil einige schon abgesetzt wurden. Durch das abgeschaltete Auto-Commit wird das executeUpdate also noch nicht wirklich ausgeführt.

Der zweite Befehl ist das Aufbuchen des Betrags. Erst wenn auch das erfolgte, ist die Transaktion vollständig. Das Commit führt dann alles wirklich aus. Wenn das Auto-Commit abgeschaltet bleibt, startet nach dem Commit sofort wieder eine neue Transaktion. Geht bei dem ganzen Vorgang etwas schief, so muss man in der Fehlerbehandlung auf jeden Fall ein Rollback durchführen. Das sorgt dann dafür, dass die Datenbank unverändert bleibt.

Zum Abschluss sehen Sie den Zustand der Datenbank-Tabelle nach einer erfolgreichen Transaktion. Das Geld wurde überwiesen!

Ein weiteres wichtiges Konzept sind die Stored Procedures. Dabei handelt es sich um gespeicherte Abläufe in der Datenbank, die von außen ausgeführt werden können. Sie reduzieren prinzipiell die Menge an Datenverkehr zwischen Client und Server, entlasten die Fachlogik und sorgen für konsistente, wiederholbare Vorgänge.

Eine Stored Procedure ist eine Funktion eines Datenbankmanagementsystems, die es ermöglicht, ganze Abläufe von Anweisungen aufgerufen vom Datenbank-Client auszuführen. Sie ist also ein eigenständiger Befehl, der eine Reihe von bereits gespeicherten Befehlen nacheinander ausführt. Diese Prozeduren werden im sogenannten Data-Dictionary der jeweiligen Datenbank gespeichert. Ein Vorteil der Verwendung von gespeicherten Prozeduren ist, dass sie die Leistung verbessern können. Das passiert, weil weniger Daten zwischen dem Client und dem Datenbanksystem ausgetauscht werden müssen, und da das Datenbankmanagementsystem häufig auf leistungsfähigeren Servern läuft, können die Abläufe schneller ausgeführt werden.

Stored Procedures verwenden nicht nur die gewohnte Syntax der Abfragesprache, wie wir sie aus SQL kennen, sondern bieten auch die Möglichkeit, zusätzliche Befehle zur Steuerung des Ablaufs oder zur Auswertung von Bedingungen hinzuzufügen. Dadurch können sie mit Makrosprachen verglichen werden, die in bestimmten Anwendungsprogrammen genutzt werden. Häufig wird das verwendete SQL um spezielle, herstellerspezifische Funktionen erweitert. Inzwischen ist es sogar möglich, in Stored Procedures auch andere Programmiersprachen wie Java oder C# zu verwenden, was die Flexibilität und die Einsatzmöglichkeiten weiter erhöht.

Verschiedene Datenbanken bieten unterschiedliche Stored Procedure-Sprachen mit spezifischen Funktionen. In MySQL und MariaDB wird Structured Query Language zusammen mit Persistent Stored Modules genutzt. Diese unterstützen den Befehl DELIMITER, jedoch keine Outer-Transaktionen. Eine Outer-Transaktion ist die übergeordnete Transaktion in einer Hierarchie verschachtelter Transaktionen, welche die Konsistenz und das Commit-/Rollback-Verhalten aller darin enthaltenen Subtransaktionen steuert. PostgreSQL verwendet Procedural Language SQL, eine sehr leistungsfähige Sprache, die komplexe Datentypen und Trigger unterstützt. Ein Trigger ist eine gespeicherte Prozedur, die in einer Datenbank automatisch bei definierten Ereignissen wie INSERT, UPDATE oder DELETE ausgeführt wird. Microsoft SQL Server nutzt Transact-SQL, das erweiterte Fehlerbehandlung mit dem try-catch-Mechanismus bietet und leistungsstarke Cursors unterstützt. Oracle setzt auf Procedural Language-SQL, eine sehr umfangreiche Sprache, die Pakete, Funktionen und Trigger unterstützt. IBM DB2 verwendet SQL Procedural Language und ermöglicht komplexe Transaktionen sowie den Einsatz von Cursor. Ein Cursor ist ein Datenbankobjekt, das innerhalb der Prozedur dazu dient, zeilenweise über das Abfrageergebnis zu iterieren und jede Zeile einzeln zu verarbeiten. Firebird nutzt Procedural SQL, das für die Fehlerbehandlung „when-do“-Blöcke bietet. SQLite hingegen hat keine native Unterstützung für Stored Procedures, wird jedoch oft durch externe Skripte ergänzt.

Obwohl Stored Procedures schnellen Zugriff ermöglichen, sollte Geschäftslogik nicht in ihnen ausgelagert werden. Das würde das Schichtenmodell der Anwendung verletzen und eine unnötige Abhängigkeit von der Datenbank schaffen, was bei Skalierung oder Datenbankwechsel problematisch sein kann. Die Grafik verdeutlicht, wie das Schichtenmodell einer Anwendung aufgebaut ist, von der GUI über die Geschäftslogik bis hin zum Datenzugriff und der Datenbank. Geschäftslogik sollte in einer höheren Ebene bleiben, um die Flexibilität und Wartbarkeit der Anwendung zu gewährleisten. Das Schichtenmodell hilft, jede Ebene ihrer Aufgabe gemäß zuzuordnen und die Unabhängigkeit der Anwendung von der Datenbank zu bewahren.

In eine Stored Procedure gehören vor allem kombinierte SQL-Abfragen, die häufig zusammen verwendet werden. Auch Abfragen, bei denen vorher Prüfungen durchgeführt werden müssen, können sinnvoll in einer Stored Procedure zusammengefasst werden. Ein weiteres typisches Einsatzgebiet ist das Anstoßen von zusätzlichen Aktionen, wie zum Beispiel das Protokollieren von bestimmten Vorgängen. Wenn also regelmäßig bestimmte Aktionen durchgeführt werden sollen, wie etwa das Cross-Cutting-Concern des Logging, ist eine Stored Procedure eine gute Wahl, um diese Abläufe effizient zu gestalten. Dies kann dann die Fachlogik sinnvoll entlasten.

In diesem Beispiel sehen wir eine Stored Procedure, die dazu dient, einen neuen Privatkunden in die Datenbank einzufügen. Zuerst wird der Kunde in der Tabelle „kunde“ mit den entsprechenden Informationen wie Kundennummer, Adresse und Kreditkarteninformation hinzugefügt. Anschließend erfolgt das Einfügen des Privatkunden in eine separate Tabelle, wobei dieselbe Kundennummer sowie der Vorname und Nachname des Kunden gespeichert werden. Auf diese Weise wird die Vererbungshierarchie der schwachen Entität des Privatkunden automatisch konsistent umgesetzt. Das Besondere an dieser Stored Procedure ist also, dass sie beide Schritte, das Einfügen in zwei verschiedene Tabellen, automatisiert und in einem Aufruf zusammenfasst.

In phpMyAdmin lassen sich Stored Procedures visuell darstellen und verwalten. Dadurch können Änderungen unkompliziert vorgenommen und Abläufe getestet werden. Hier ist die Funktion aus unserer Aufgabenstellung eingefügt.

Und hier sehen Sie den Aufruf der Procedure über CALL als SQL-Befehl. Der Aufruf schlägt jedoch aufgrund eines Problems in der Adress-ID fehl.

Der Fehler tritt auf, weil beim Erstellen eines neuen Privatkunden zunächst ein neuer Kunde angelegt wird. Dies entspricht der Vererbung in Java. Das wurde im ursprünglichen Befehl berücksichtigt. Jedoch benötigt der Kunde auch eine Adresse, und zwischen der Tabelle „kunde“ und der Tabelle „adresse“ besteht eine Eins-zu-Eins-Beziehung. Das bedeutet, dass eine Adresse nicht mehreren Kunden zugeordnet werden kann. Wenn also eine Adresse bereits für einen anderen Kunden verwendet wird, entsteht der Fehler. Um dieses Problem zu lösen, muss die Stored Procedure so angepasst werden, dass zusätzlich zu den Kundendaten auch die Adressdaten als Eingabeparameter übergeben werden. Dadurch müsste auch automatisch für den neuen Kunden eine neue Adresse in der Tabelle „adresse“ angelegt werden. Spätestens hier sieht man, dass immer mehr Fachlogik unerwünscht in die Datenbank ausgelagert wird, wenn man sich für den Einsatz von Stored Procedures entscheidet.

Um das Problem aus unserem Beispiel zu erweitern, könnte eine Stored Procedure für das Logging von Kundenlöschungen implementiert werden. Wenn ein Kunde gelöscht wird, soll das nicht nur in der Tabelle „kunde“ durchgeführt werden, sondern auch protokolliert werden, damit alle Löschvorgänge nachvollziehbar sind. Dazu wird zunächst eine separate Log-Tabelle erstellt. Diese Tabelle enthält eine Log-ID, die automatisch erhöht wird, die Kundennummer, den Zeitstempel der Löschung sowie eine Aktion, die beschreibt, dass der Kunde gelöscht wurde. Das Logging hilft, eine vollständige Historie der Aktionen zu führen und trägt dazu bei, Fehler und unbefugte Löschvorgänge zu verhindern, indem jede Löschung nachvollziehbar bleibt. Der Zugriff auf diese Tabellenkopie könnte nur für Super-Admins erlaubt werden.

Um das Logging der Kundenlöschungen zu automatisieren, kann ein Trigger erstellt werden. Dieser Trigger wird aktiviert, bevor ein Kunde aus der Kundentabelle gelöscht wird. Der Trigger sorgt dafür, dass automatisch ein Eintrag in die Log-Tabelle „kunden-log“ eingefügt wird. Im Beispiel wird der Trigger „Kunde-Loeschen-Protokoll“ definiert, der vor jeder Löschung eines Kunden ausgelöst wird. Dabei wird die Kundennummer des zu löschenden Kunden in die Log-Tabelle eingefügt, zusammen mit der Aktion „Kunde gelöscht“. Der Trigger sorgt dafür, dass jede Löschung eines Kunden automatisch dokumentiert wird.